package com.zyh.homework.test2;

import com.zyh.utils.JDBCUtil;
import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.Scanner;

/**
 * @Author zyh
 * @Date 2024/8/22 18:27
 * @Description
 */

public class Test2 {
    public static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/emp?useUnicode=true&useSSL=false&characterEncoding=UTF8";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";

    // 加载 MySQL 驱动
    static {
        //加载 MySQL　驱动
        try {
            Class.forName(MYSQL_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 创建连接的方法
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 添加数据
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        for (int i = 0; i < 3; i++) {
            Scanner input = new Scanner(System.in);
            System.out.println("请输入员工姓名：");
            String name = input.next();
            System.out.println("请输入员工年龄：");
            String age = input.next();
            System.out.println("请输入员工体重：");
            String weight = input.next();
            System.out.println("请输入员工地址：");
            String address = input.next();
            connection = getConnection();
            statement = connection.createStatement();
            String sql = "insert into emp value (" + null + "," + "'" + name + "'" + "," + Integer.parseInt(age) + "," + Integer.parseInt(weight) + "," + "'" + address + "'" + ")";
            int count = statement.executeUpdate(sql);
            System.out.println(count == 1 ? "添加成功" : "添加失败");
            JDBCUtil.close(statement, connection);
        }
        // 关闭连接
        JDBCUtil.close(statement, connection);
    }

    @Test
    public void test3() throws SQLException {
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        String sql = "select address, count(*) from emp group by address";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            String address = rs.getString("address");
            int num = rs.getInt("count(*)");
            System.out.println(address + "--" + num);
        }
        // 关闭连接
        JDBCUtil.close(rs, statement, connection);
    }

    @Test
    public void test4() throws Exception {
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        String sql = "select * from emp where e_name like '李__' and address='北京'";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int eid = resultSet.getInt("eid");
            String name = resultSet.getString("e_name");
            int age = resultSet.getInt("e_age");
            int weight = resultSet.getInt("weight");
            String address = resultSet.getString("address");
            System.out.println(eid + "--" + name + "--" + age + "--" + weight + "--" + address);
        }
        // 关闭连接
        JDBCUtil.close(resultSet, statement, connection);
    }

    @Test
    public void test5() throws Exception {
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        String sql = "select * from emp where e_age+3>30 order by e_age desc";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int eid = resultSet.getInt("eid");
            String name = resultSet.getString("e_name");
            int age = resultSet.getInt("e_age");
            int weight = resultSet.getInt("weight");
            String address = resultSet.getString("address");
            System.out.println(eid + "--" + name + "--" + age + "--" + weight + "--" + address);
        }
        // 关闭连接
        JDBCUtil.close(resultSet,statement, connection);
    }
}
